package it.unibz.easytravel.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DebtsDbAdapter {

	private static final String TAG = "DebtsDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;

	private static final String TABLE_NAME = "debts";

	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE = "create table debts ("
			+ "_id integer primary key autoincrement, " + "travel_id integer, "
			+ "amount integer, " + "creditor integer, " + "debtor integer);";

	public static void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE);
	}

	public static void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS debts");
		onCreate(db);
	}

	public DebtsDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public DebtsDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	/**
	 * method deletes all entries form table
	 */
	public void delete() {
		Log.i(TAG, "deleteTable");
		mDb.execSQL("DELETE FROM debts");
	}

	public long createDebt(int travelId, int amount, int creditor, int debtor) {
		Log.i(TAG, "createDebt");
		ContentValues values = new ContentValues();
		values.put("travel_id", travelId);
		values.put("amount", amount);
		values.put("creditor", creditor);
		values.put("debtor", debtor);
		return mDb.insert("debts", null, values);
	}

	/**
	 * SQL = SELECT _id FROM debts WHERE creditor = creditor AND debtor = debtor
	 * AND travel_id = travel_id
	 * 
	 * @param travelId
	 * @param creditor
	 * @param debtor
	 * @return id of found debt relation and -1 if relation does not exist
	 */
	public int getDebtRelationID(int travelId, int creditor, int debtor) {
		Log.i(TAG, "getDebtRelationID");
		String where = "travel_id=" + travelId + " and creditor=" + creditor
				+ " and debtor=" + debtor;
		Cursor cursor = mDb.query(TABLE_NAME, new String[] { "_id" }, where,
				null, null, null, null);
		if (!cursor.moveToFirst())
			return -1;
		return cursor.getInt(0);
	}

	/**
	 * SQL = SELECT amount FROM debts WHERE _id = nodeId
	 * 
	 * @param nodeId
	 * @return spentAmount of found debt relation and -1 if relation does not
	 *         exist
	 */
	public int getAmountSpent(int nodeId) {
		Log.i(TAG, "getAmountSpent");
		String where = "_id=" + nodeId;
		Cursor cursor = mDb.query(TABLE_NAME, new String[] { "amount" }, where,
				null, null, null, null);
		if (!cursor.moveToFirst())
			return -1;
		return cursor.getInt(0);
	}

	/**
	 * SLQ = UPDATE debts SET amount = amount WHERE _id = userId AND traveler_id
	 * = travelId
	 * 
	 * @param nodeId
	 * @param travelId
	 * @param amount
	 */
	public void setAmountSpent(int nodeId, int amount) {
		Log.i(TAG, "setAmountSpent");
		String where = "_id=" + nodeId;
		ContentValues values = new ContentValues();
		values.put("amount", amount);
		mDb.update(TABLE_NAME, values, where, null);
	}

	/**
	 * SQL1 = SELECT amount AS am FROM debts WHERE _id=nodeId AND travel_id =
	 * travelId SLQ2 = UPDATE debts SET amount = am+amountToAdd WHERE _id =
	 * nodeId AND traveler_id = travelId
	 * 
	 * @param userId
	 * @param travelId
	 * @param amountToAdd
	 */
	public void addToDebtRelation(int nodeId, int amountToAdd) {
		Log.i(TAG, "addToDebtRelation");
		int moneyFlow = getAmountSpent(nodeId);
		setAmountSpent(nodeId, moneyFlow + amountToAdd);
	}

	/**
	 * SLQ = DELETE FROM debts WHERE _id = nodeId
	 * 
	 * @param userId
	 */
	public void deleteNode(int userId) {
		Log.i(TAG, "deleteNode");
		String where = "_id=" + userId;
		mDb.delete(TABLE_NAME, where, null);
	}

	/**
	 * Return a Cursor over the list of all notes in the database
	 * 
	 * @return Cursor over all debts
	 */
	public Cursor getAllDebts(int current_travel_id) {
		Log.i(TAG, "getAllDebts");
		return mDb.query("debts", new String[] { "_id", "creditor", "debtor",
				"amount" }, "travel_id=" + current_travel_id, null, null, null,
				null);
	}

}
